package io.paper.service.impl;

import java.io.IOException;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.SQLException;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.springframework.stereotype.Service;

import com.alibaba.druid.util.StringUtils;

import interbase.interclient.PreparedStatement;
import interbase.interclient.ResultSet;
import io.paper.entity.CustomerEntity;
import io.paper.entity.MoonBillEntity;
import io.paper.service.MoonBillService;
import io.paper.utils.CodeSetUtil;
import io.renren.utils.util.DatabaseUtil;
import io.renren.utils.util.exception.XMTException;



/**
 * 系统用户
 * 
 * @author chenshun
 * @email sunlightcs@gmail.com
 * @date 2016年9月18日 上午9:46:09
 */
@Service("moonBillService")
public class MoonBillServiceImpl implements MoonBillService {

	private static Logger logger = Logger.getLogger(MoonBillServiceImpl.class);
	
	private static SimpleDateFormat TIME_ADF = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
	private static DecimalFormat df = new DecimalFormat("#0.00");
			
	@Override
	public List<MoonBillEntity> queryBilllist(Map<String, Object> map) {
		Connection con = null;
		String startRow = map.get("startRow") +"";
		String endRow = map.get("endRow")+"" ;
		MoonBillEntity param = (MoonBillEntity) map.get("para");
		
		String cUSTOMERID = param.getcUSTOMERID();
		String startDate = CodeSetUtil.changeDateFomat(param.getStartDate());
		String endDate = CodeSetUtil.changeDateFomat(param.getEndDate());
		
		String sql = "select a.trandate as invoiceDate, a.invoiceno,a.salespeopleid,b.sono,c.trandate as soDate,b.custref,b.customercode,e.CARTONNAME,"
				+ "b.quality,b.lengt_1 as lengt,b.width_1 as width,b.high_1 as high, b.unitid ,b.quantity,b.soquantity,d.invoiceqty,b.price,b.weight,"
				+ "b.remark from saleinv a,saleinvd b,so c, sodetail d left join carton e on e.cartonid = d.cartonid "
				+ "where a.invoiceno=b.invoiceno and b.sono=c.sono and c.sono=d.sono and b.customercode=d.customercode ";
		if(!StringUtils.isEmpty(cUSTOMERID)){
			sql+="and c.CUSTOMERID like '%"+cUSTOMERID+"%' ";
		}
		if(!StringUtils.isEmpty(startDate)){
			sql+="and c.trandate >= '"+startDate+"' ";
		}
		if(!StringUtils.isEmpty(endDate)){
			sql+="and c.trandate <= '"+endDate+"' ";
		}
		sql+= "order by a.trandate asc,b.customercode asc ";
		if(!"null".equals(startRow) && !"null".equals(endRow) && !StringUtils.isEmpty(startRow) && !StringUtils.isEmpty(endRow)){
			sql+= "rows #{startRow} to #{endRow} ";
			sql=CodeSetUtil.getDbParam(sql, startRow,"startRow");
			sql=CodeSetUtil.getDbParam(sql, endRow,"endRow");
		}
        PreparedStatement pstmt;
        List<MoonBillEntity> result = new ArrayList<MoonBillEntity>();
		try {
			logger.info("queryBilllist:"+sql);
			con = DatabaseUtil.getConnection("paper");
			pstmt = (PreparedStatement)con.prepareStatement(CodeSetUtil.getGBKtoISO(sql));
            ResultSet rs = (ResultSet) pstmt.executeQuery();
            while (rs.next()) {
            	MoonBillEntity vo = new MoonBillEntity();
            	vo.setInvoiceDate(rs.getTimestamp("INVOICEDATE"));
            	vo.setINVOICENO(rs.getString("INVOICENO"));
            	vo.setSALESPEOPLEID(CodeSetUtil.getISOtoGBK(rs.getString("SALESPEOPLEID")));
            	vo.setSONO(rs.getString("SONO"));
            	vo.setSoDate(rs.getTimestamp("SODATE"));
            	vo.setCUSTREF(rs.getString("CUSTREF"));
            	vo.setCUSTOMERCODE(CodeSetUtil.getISOtoGBK(rs.getString("CUSTOMERCODE")));
            	vo.setCARTONNAME(CodeSetUtil.getISOtoGBK(rs.getString("CARTONNAME")));
            	vo.setQUALITY(CodeSetUtil.getISOtoGBK(rs.getString("QUALITY")));
            	vo.setLength(rs.getString("LENGT"));
            	vo.setWidth(rs.getString("WIDTH"));
            	vo.setHigh(rs.getString("HIGH"));
            	vo.setUNITID(CodeSetUtil.getISOtoGBK(rs.getString("UNITID")));
            	BigDecimal quanitity = (new BigDecimal(rs.getString("QUANTITY"))).setScale(2,BigDecimal.ROUND_HALF_UP);
            	vo.setQUANTITY(quanitity);
            	BigDecimal soQuantity = (new BigDecimal(rs.getString("SOQUANTITY"))).setScale(2,BigDecimal.ROUND_HALF_UP);
            	vo.setSOQUANTITY(soQuantity);
            	BigDecimal invoiceqty = (new BigDecimal(rs.getString("INVOICEQTY"))).setScale(2,BigDecimal.ROUND_HALF_UP);
            	vo.setINVOICEQTY(invoiceqty);
            	BigDecimal price = (new BigDecimal(rs.getString("PRICE"))).setScale(2,BigDecimal.ROUND_HALF_UP);
            	vo.setPrice(price);
            	BigDecimal weight = (new BigDecimal(rs.getString("WEIGHT"))).setScale(2,BigDecimal.ROUND_HALF_UP);
            	vo.setWeight(weight);
            	vo.setRemark(CodeSetUtil.getISOtoGBK(rs.getString("REMARK")));
            	vo.setNoInvoiceQty(soQuantity.subtract(invoiceqty));
            	vo.setTotalPrice(price.multiply(quanitity));
            	vo.setTotalWeight(weight.multiply(quanitity));
            	result.add(vo);
            }
		} catch (XMTException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
            try {
                if (con != null) {
                    con.close();
                    con = null;
                }
            } catch (Exception exception) {
            	exception.printStackTrace();
            }
        }
		return result;
	}

	@Override
	public int queryTotal(Map<String, Object> map) {
		Connection con = null;
		MoonBillEntity param = (MoonBillEntity) map.get("para");
		String cUSTOMERID = param.getcUSTOMERID();
		String startDate = CodeSetUtil.changeDateFomat(param.getStartDate());
		String endDate = CodeSetUtil.changeDateFomat(param.getEndDate());
		String sql = "select count(0) as totalNum from saleinv a,saleinvd b,so c, sodetail d left join carton e on e.cartonid = d.cartonid "
				+ "where a.invoiceno=b.invoiceno and b.sono=c.sono and c.sono=d.sono and b.customercode=d.customercode ";
		if(!StringUtils.isEmpty(cUSTOMERID)){
			sql+="and c.CUSTOMERID like '%"+cUSTOMERID+"%' ";
		}
		if(!StringUtils.isEmpty(startDate)){
			sql+="and c.trandate >= '"+startDate+"' ";
		}
		if(!StringUtils.isEmpty(endDate)){
			sql+="and c.trandate <= '"+endDate+"' ";
		}
        PreparedStatement pstmt;
        int totalNum=0;
		try {
			logger.info("countBilllist:"+sql);
			con = DatabaseUtil.getConnection("paper");
			pstmt = (PreparedStatement)con.prepareStatement(CodeSetUtil.getGBKtoISO(sql));
            ResultSet rs = (ResultSet) pstmt.executeQuery();
            while (rs.next()) {
            	totalNum=rs.getInt("totalNum");
            }
		} catch (XMTException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
            try {
                if (con != null) {
                    con.close();
                    con = null;
                }
            } catch (Exception exception) {
            	exception.printStackTrace();
            }
        }
		return totalNum;
	}

	/**
	 * 查询客户信息
	 */
	@Override
	public List<CustomerEntity> queryCustomerlist(Map<String, Object> map) {
		Connection con = null;
		CustomerEntity param = (CustomerEntity) map.get("para");
		String cUSTOMERID = param.getCUSTOMERID();
		
		String sql = "select * from CUSTOMER a where 1=1  ";
		if(!StringUtils.isEmpty(cUSTOMERID)){
			sql+="and a.CUSTOMERID like '%"+cUSTOMERID+"%' ";
		}
		sql+= "order by a.CUSTOMERID asc ";
        PreparedStatement pstmt;
        List<CustomerEntity> result = new ArrayList<CustomerEntity>();
		try {
			logger.info("queryCustomerlist:"+sql);
			con = DatabaseUtil.getConnection("paper");
			pstmt = (PreparedStatement)con.prepareStatement(CodeSetUtil.getGBKtoISO(sql));
            ResultSet rs = (ResultSet) pstmt.executeQuery();
            while (rs.next()) {
            	CustomerEntity vo = new CustomerEntity();
            	vo.setCUSTOMERID(CodeSetUtil.getISOtoGBK(rs.getString("CUSTOMERID")));
            	vo.setCUSTOMERNAME(CodeSetUtil.getISOtoGBK(rs.getString("CUSTOMERNAME")));
            	vo.setCUSTOMERSHORTNAME(CodeSetUtil.getISOtoGBK(rs.getString("CUSTOMERSHORTNAME")));
            	vo.setCUSTOMERTYPEID(CodeSetUtil.getISOtoGBK(rs.getString("CUSTOMERTYPEID")));
            	vo.setAREAID(CodeSetUtil.getISOtoGBK(rs.getString("AREAID")));
            	vo.setSHIPADDRESS(CodeSetUtil.getISOtoGBK(rs.getString("SHIPADDRESS")));
            	vo.setCONTACTOR(CodeSetUtil.getISOtoGBK(rs.getString("CONTACTOR")));
            	vo.setPHONE1(CodeSetUtil.getISOtoGBK(rs.getString("PHONE1")));
            	vo.setFAX(CodeSetUtil.getISOtoGBK(rs.getString("FAX")));
            	vo.setSALESPEOPLEID(CodeSetUtil.getISOtoGBK(rs.getString("SALESPEOPLEID")));
            	vo.setCURRID(CodeSetUtil.getISOtoGBK(rs.getString("CURRID")));
            	vo.setPAYTYPEID(CodeSetUtil.getISOtoGBK(rs.getString("PAYTYPEID")));
            	vo.setPAYTERMID(CodeSetUtil.getISOtoGBK(rs.getString("PAYTERMID")));
            	result.add(vo);
            }
		} catch (XMTException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
            try {
                if (con != null) {
                    con.close();
                    con = null;
                }
            } catch (Exception exception) {
            	exception.printStackTrace();
            }
        }
		return result;
	}

	@Override
	public boolean exportMoonBillExcel(Map<String, Object> paramMap,OutputStream out) {
		int totalRecord = queryTotal(paramMap);
		int page_size=100;
		// 第一步，创建一个webbook，对应一个Excel文件  
		HSSFWorkbook wb = new HSSFWorkbook();  
		try {
	        // 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet  
			Sheet sheet = wb.createSheet("月账单记录"); 
	        sheet.setColumnWidth(0,10*2*256);
	        sheet.setColumnWidth(1,10*2*256);
	        sheet.setColumnWidth(2,10*2*256);
	        sheet.setColumnWidth(3,10*2*256);
	        sheet.setColumnWidth(4,10*2*256);
	        sheet.setColumnWidth(5,10*2*256);
	        sheet.setColumnWidth(6,10*2*256);
	        sheet.setColumnWidth(7,10*2*256);
	        sheet.setColumnWidth(8,10*2*256);
	        sheet.setColumnWidth(9,10*2*256);
	        sheet.setColumnWidth(10,10*2*256);
	        sheet.setColumnWidth(11,10*2*256);
	        sheet.setColumnWidth(12,10*2*256);
	        // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short  
	        //字体样式
			Font fontTitle = wb.createFont();
			fontTitle.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//粗体显示
			//fontTitle.setColor(HSSFColor.WHITE.index);
			//创建标题样式
			CellStyle styleTitle = wb.createCellStyle();
			styleTitle.setFont(fontTitle);
			styleTitle.setFillBackgroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
			styleTitle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
			styleTitle.setFillPattern(CellStyle.SOLID_FOREGROUND);
			//创建数据块样式
			CellStyle whiteContent = wb.createCellStyle();
			whiteContent.setFillPattern(CellStyle.SOLID_FOREGROUND);
			//设置标题行
			Row rowTitle = sheet.createRow(0); //行
			Cell cellTitle = null;
			//送货日期
			cellTitle = rowTitle.createCell((short) 0);
			cellTitle.setCellValue("送货日期");
			cellTitle.setCellStyle(styleTitle);
			//送货单号
			cellTitle = rowTitle.createCell((short) 1);
			cellTitle.setCellValue("送货单号");
			cellTitle.setCellStyle(styleTitle);
			//发票编码
			cellTitle = rowTitle.createCell((short) 2);
			cellTitle.setCellValue("发票编码");
			cellTitle.setCellStyle(styleTitle);
			//订单编号
			cellTitle = rowTitle.createCell((short) 3);
			cellTitle.setCellValue("订单编号");
			cellTitle.setCellStyle(styleTitle);
			//订单日期
			cellTitle = rowTitle.createCell((short) 4);
			cellTitle.setCellValue("订单日期");
			cellTitle.setCellStyle(styleTitle);
			//客户PO
			cellTitle = rowTitle.createCell((short) 5);
			cellTitle.setCellValue("客户PO");
			cellTitle.setCellStyle(styleTitle);
			//客方货号
			cellTitle = rowTitle.createCell((short) 6);
			cellTitle.setCellValue("客方货号");
			cellTitle.setCellStyle(styleTitle);
			//盒式
			cellTitle = rowTitle.createCell((short) 7);
			cellTitle.setCellValue("盒式");
			cellTitle.setCellStyle(styleTitle);
			//纸质
			cellTitle = rowTitle.createCell((short) 8);
			cellTitle.setCellValue("纸质");
			cellTitle.setCellStyle(styleTitle);
			//规格
			cellTitle = rowTitle.createCell((short) 9);
			cellTitle.setCellValue("规格");
			cellTitle.setCellStyle(styleTitle);
			//数量
			cellTitle = rowTitle.createCell((short) 10);
			cellTitle.setCellValue("数量");
			cellTitle.setCellStyle(styleTitle);
			//订单数量
			cellTitle = rowTitle.createCell((short) 11);
			cellTitle.setCellValue("订单数量");
			cellTitle.setCellStyle(styleTitle);
			//已送数量
			cellTitle = rowTitle.createCell((short) 12);
			cellTitle.setCellValue("已送数量");
			cellTitle.setCellStyle(styleTitle);
			
			//未送货数
			cellTitle = rowTitle.createCell((short) 13);
			cellTitle.setCellValue("未送货数");
			cellTitle.setCellStyle(styleTitle);
			//单价
			cellTitle = rowTitle.createCell((short) 14);
			cellTitle.setCellValue("单价");
			cellTitle.setCellStyle(styleTitle);
			//金额
			cellTitle = rowTitle.createCell((short) 15);
			cellTitle.setCellValue("金额");
			cellTitle.setCellStyle(styleTitle);
			//单重
			cellTitle = rowTitle.createCell((short) 16);
			cellTitle.setCellValue("单重");
			cellTitle.setCellStyle(styleTitle);
			//重量
			cellTitle = rowTitle.createCell((short) 17);
			cellTitle.setCellValue("重量");
			cellTitle.setCellStyle(styleTitle);
			//备注
			cellTitle = rowTitle.createCell((short) 18);
			cellTitle.setCellValue("备注");
			cellTitle.setCellStyle(styleTitle);
	        int num = 0;
	        Cell cell = null;
	        int export_times = totalRecord % page_size > 0 ? totalRecord / page_size
	    			+ 1 : totalRecord / page_size;
	    	// 按次数将数据写入文件
	        List<MoonBillEntity>  dtoList=new ArrayList<MoonBillEntity>();
	    	for (int j = 0; j < export_times; j++) {
	    		//参数
	    		paramMap.put("startRow", j*page_size+1);
	    		paramMap.put("endRow", (j+1)*page_size);
	    		dtoList= queryBilllist(paramMap);
	    		int len = dtoList.size() < page_size ? dtoList.size() : page_size;
	    		for (int i = 0; i < len; i++){
		        	num ++;
		        	MoonBillEntity dto = dtoList.get(i);
		        	//SO基本信息
		        	num = setWxInfoRow(sheet, cell, whiteContent, num, dto);
		        }
	    		dtoList.clear(); // 每次存储len行，用完了将内容清空，以便内存可重复利用
	    	}
	        //写入输出流
	        wb.write(out);
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		} finally{
			try {
				out.close();
				wb.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
		return true;
	}
	
	/**
	 * 设置SO基本信息行
	 */
	@SuppressWarnings("deprecation")
	public int setWxInfoRow(Sheet sheet, Cell cell, CellStyle styleTitle, int rowNum, MoonBillEntity dto){
		try {
			Row row = sheet.createRow(rowNum); //行
	    	//送货日期
			Date invoiceDate = dto.getInvoiceDate();
			String invoiceTime = null == invoiceDate ? "" : TIME_ADF.format(invoiceDate);
			cell = row.createCell((short) 0);
			cell.setCellValue(invoiceTime);
			//送货单号
			String invoiceno = dto.getINVOICENO();
			cell = row.createCell((short) 1);
			cell.setCellValue(null==invoiceno?"":invoiceno);
			//发票编码
			String sALESPEOPLEID = dto.getSALESPEOPLEID();
			cell = row.createCell((short) 2);
			cell.setCellValue(null==sALESPEOPLEID?"":sALESPEOPLEID);
			//订单编号
			String sONO = dto.getSONO();
			cell = row.createCell((short) 3);
			cell.setCellValue(null==sONO?"":sONO);
			//订单日期
			Date soDate = dto.getSoDate();
			String soTime = null == soDate ? "" : TIME_ADF.format(soDate);
			cell = row.createCell((short) 4);
			cell.setCellValue(soTime);
			//客户PO
			String cUSTREF = dto.getCUSTREF();
			cell = row.createCell((short) 5);
			cell.setCellValue(null==cUSTREF?"":cUSTREF);
			//客方货号
			String cUSTOMERCODE = dto.getCUSTOMERCODE();
			cell = row.createCell((short) 6);
			cell.setCellValue(null==cUSTOMERCODE?"":cUSTOMERCODE);
			//盒式
			String cARTONNAME = dto.getCARTONNAME();
			cell = row.createCell((short) 7);
			cell.setCellValue(null==cARTONNAME?"":cARTONNAME);
			//纸质
			String qUALITY = dto.getQUALITY();
			cell = row.createCell((short) 8);
			cell.setCellValue(null==qUALITY?"":qUALITY);
			//规格
			String sizeFormat = "";
			if("0".equals(dto.getHigh()) || "0.0".equals(dto.getHigh()) ){
				sizeFormat = dto.getLength()+"x"+dto.getWidth()+" "+dto.getUNITID();
			}else{
				sizeFormat = dto.getLength()+"x"+dto.getWidth()+"x"+dto.getHigh()+" "+dto.getUNITID();
			}
			cell = row.createCell((short) 9);
			cell.setCellValue(null==sizeFormat?"":sizeFormat);
			//数量
			BigDecimal qUANTITY = dto.getQUANTITY();
			String qUANTITYStr = df.format(null==qUANTITY?0:qUANTITY);
			cell = row.createCell((short) 10);
			cell.setCellValue(qUANTITYStr);
			//订单数量
			BigDecimal sOQUANTITY = dto.getSOQUANTITY();
			String sOQUANTITYStr = df.format(null==sOQUANTITY?0:sOQUANTITY);
			cell = row.createCell((short) 11);
			cell.setCellValue(sOQUANTITYStr);
			//已送数量
			BigDecimal iNVOICEQTY = dto.getINVOICEQTY();
			String iNVOICEQTYStr = df.format(null==iNVOICEQTY?0:iNVOICEQTY);
			cell = row.createCell((short) 12);
			cell.setCellValue(iNVOICEQTYStr);
			//未送货数
			BigDecimal noInvoiceQty = dto.getINVOICEQTY();
			String noInvoiceQtyStr = df.format(null==noInvoiceQty?0:noInvoiceQty);
			cell = row.createCell((short) 13);
			cell.setCellValue(noInvoiceQtyStr);
			//单价
			BigDecimal price = dto.getINVOICEQTY();
			String priceStr = df.format(null==price?0:price);
			cell = row.createCell((short) 14);
			cell.setCellValue(priceStr);
			//金额
			BigDecimal totalPrice = dto.getINVOICEQTY();
			String totalPriceStr = df.format(null==totalPrice?0:totalPrice);
			cell = row.createCell((short) 15);
			cell.setCellValue(totalPriceStr);
			//单重
			BigDecimal weight = dto.getINVOICEQTY();
			String weightStr = df.format(null==weight?0:weight);
			cell = row.createCell((short) 16);
			cell.setCellValue(weightStr);
			//重量
			BigDecimal totalWeight = dto.getINVOICEQTY();
			String totalWeightStr = df.format(null==totalWeight?0:totalWeight);
			cell = row.createCell((short) 17);
			cell.setCellValue(totalWeightStr);
			//备注
			String remark = dto.getRemark();
			cell = row.createCell((short) 18);
			cell.setCellValue(null==remark?"":remark);
			
		} catch (Exception e) {
			e.printStackTrace();
			logger.info("导出数据组装错误：Error "+e.getMessage());
		}
		return rowNum;
	}
	
}
